package com.linln.modules.system.repository;

import com.linln.modules.system.domain.Uv;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * @author 小懒虫
 * @date 2020/03/12
 */
public interface UvRepository extends BaseRepository<Uv, Long> {
    @Query(nativeQuery = true, value = "SELECT COUNT(DISTINCT uuid)FROM or_uv WHERE TO_DAYS( NOW( ) ) - TO_DAYS(create_date ) <= 1  ")
    int findCountBefore();
    @Query(nativeQuery = true, value = "SELECT COUNT(DISTINCT uuid)FROM or_uv WHERE TO_DAYS( NOW( ) ) = TO_DAYS(create_date )")
    int findCountNow();
    @Query(nativeQuery = true, value = " select SUBSTRING_INDEX(location,'-',-1) as 'name',COUNT(1) as 'value' from or_uv" +
            "    where location is not NULL group by SUBSTRING_INDEX(location,'-',-1)")
    List<Map> findMapCity();

    /**
     *  获取 前10 的入口地址
     * @return
     */
    @Query(nativeQuery = true, value = "select now_url as 'url',COUNT(1) AS 'value' FROM or_uv  WHERE TO_DAYS(create_date ) >= TO_DAYS(str_to_date(?1,'%Y-%m-%d') ) and TO_DAYS(create_date ) <=TO_DAYS(str_to_date(?2,'%Y-%m-%d') ) GROUP BY now_url ORDER BY value Desc limit 10")
    List<Map> findNowUrlTop10(String day1,String day2);
    /**
     *  获取 前10 的来源地址
     * @return
     */
    @Query(nativeQuery = true, value = "select SUBSTRING_INDEX(from_url,'/',3) as 'url',COUNT(1) as 'value' from or_uv where from_url not LIKE '%nonelonely%'and TO_DAYS(create_date ) >= TO_DAYS(str_to_date(?1,'%Y-%m-%d') ) and TO_DAYS(create_date ) <=TO_DAYS(str_to_date(?2,'%Y-%m-%d') ) group by SUBSTRING_INDEX(from_url,'/',3)ORDER BY value Desc limit 10")
    List<Map> findFromUrlTop10(String day1,String day2);

    @Query(nativeQuery = true, value = "select DATE_FORMAT(create_date,'%H') hours,count(DISTINCT uuid) count from or_uv WHERE   TO_DAYS(create_date ) >= TO_DAYS(str_to_date(?1,'%Y-%m-%d') ) and TO_DAYS(create_date ) <=TO_DAYS(str_to_date(?2,'%Y-%m-%d') ) group by hours;")
    List<Map> findUv(String day1,String day2);

    @Query(nativeQuery = true, value = "select DATE_FORMAT(create_date,'%H') hours,count(1) count from or_uv WHERE   TO_DAYS(create_date ) >= TO_DAYS(str_to_date(?1,'%Y-%m-%d') ) and TO_DAYS(create_date ) <=TO_DAYS(str_to_date(?2,'%Y-%m-%d') ) group by hours;")
    List<Map> findPv(String day1,String day2);

    @Query(nativeQuery = true, value = "SELECT COUNT(DISTINCT ip)FROM or_uv WHERE TO_DAYS( NOW( ) ) = TO_DAYS(create_date )")
    int countIp();
}